
USE [DBAObjects]
GO

-- The following code is basically tied to rights issues that I decided to look 
-- into after reading this article: 
-- http://www.sqlservercentral.com/articles/Administration/64974/
-- While it doesn't so much cover rights it got me thinking.  The code here is 
-- directly to resolve permissions issues that could cause logons to be denied
-- because the user doesn't have sufficient rights to log their logon.  This was
-- tested with a user that had ONLY a sql logon to the server and no other 
-- rights.  That user was unable to logon.  I have added a login called
-- AuditCredLogin and a user AuditCredUser, this user exists in both the Audit
-- and Repository databases.  This login has a long nasty password assembled 
-- from 2 GUIDs that are generated locally, the password is not displayed and 
-- does not need to be know, it is NEVER used to actually login to the server, 
-- therefore is disabled.

DECLARE @pwd VARCHAR(80)

-- Generate random LONG password, we don't need to know what it is..
SELECT @pwd = CONVERT( VARCHAR(38), NEWID() ) + ' - ' + CONVERT( VARCHAR(38), NEWID() )

IF NOT EXISTS ( SELECT 'X' FROM master.sys.server_principals WHERE name = 'AuditCredLogin' )
BEGIN -- Login doesn't already exist so we need to create it.
     DECLARE @cmd varchar(8000)

     SELECT @cmd = 'CREATE LOGIN [AuditCredLogin] WITH PASSWORD = '''
                 + @pwd
                 + ''', '
                 + 'DEFAULT_DATABASE = [tempdb], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF '
     EXEC ( @cmd )
END
GO

-- Permission needed for Logon Trigger.
DECLARE @cmd varchar(8000)

SELECT @cmd = 'USE [master]; GRANT VIEW SERVER STATE TO [AuditCredLogin];'
EXEC ( @cmd )
GO

-- Disable Logon
DECLARE @cmd varchar(8000)

SELECT @cmd = 'ALTER LOGIN [AuditCredLogin] DISABLE;'
EXEC ( @cmd )
GO

IF NOT EXISTS ( SELECT 'X' FROM sys.database_principals WHERE name = 'AuditCredUser' )
BEGIN -- User doesn't already exist so we need to create it.
     DECLARE @cmd varchar(8000)

     SELECT @cmd = 'CREATE USER [AuditCredUser] FROM LOGIN [AuditCredLogin]; '
     EXEC ( @cmd )
END
GO

-- One additional point here, if this database is to be available for logging by
-- all other databases this next line should be executed, it is currently not
-- included for security reasons.  However enabling guest access to the database
-- does NOT grant grant guest access to the server and the login must already
-- have been authenticated.  Do not under any circumstances grant the guest 
-- login access to the SQL Server!!! Currently the only object exposed to the 
-- guest USER is the dbo.InsEventLog sproc.  This may be independently verified
-- by creating a login with no explicit user permisisons in the new database and
-- logging is as that user and then by executing:
-- SELECT * FROM sys.objects
-- in the new database, only the dbo.InsEventLog sproc should be displayed.

--GRANT CONNECT TO guest
--GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CfgCtrl]') AND type in (N'U'))
BEGIN
     DROP TABLE dbo.CfgCtrl
END
GO

CREATE TABLE dbo.CfgCtrl(
     CfgCtrlKey         varchar(30)   NOT NULL,
     CfgCtrlValue       varchar(8000)     NULL,
     CfgCtrlDescription varchar(128)  NOT NULL,
     LastModDt          datetime      NOT NULL DEFAULT (getdate()),
     CONSTRAINT [PK_CfgCtrl] PRIMARY KEY CLUSTERED ( CfgCtrlKey ASC )
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Idenitifier of a particular Configuration Control Key.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CfgCtrl', @level2type=N'COLUMN',@level2name=N'CfgCtrlKey'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Value of the configuration control key.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CfgCtrl', @level2type=N'COLUMN',@level2name=N'CfgCtrlValue'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User-friendly description of the configuration control key.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CfgCtrl', @level2type=N'COLUMN',@level2name=N'CfgCtrlDescription'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The last date/time the record was changed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CfgCtrl', @level2type=N'COLUMN',@level2name=N'LastModDt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database level configuration control data.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CfgCtrl'
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventLog]') AND type in (N'U'))
BEGIN
     DROP TABLE dbo.EventLog
END
GO

CREATE TABLE dbo.EventLog(
     EventLogId        bigint IDENTITY(1,1) NOT NULL,
     EventLogTypeId    int NOT NULL,
     LogDt             datetime NOT NULL DEFAULT (getdate()),
     HostName          varchar(128) NOT NULL DEFAULT (host_name()),
     LoginName         varchar(128) NOT NULL DEFAULT (suser_sname()),
     DatabaseUserName  varchar(128) NULL DEFAULT (user_name()),
     DatabaseName      varchar(128) NULL DEFAULT (db_name()),
     AppName           varchar(128) NULL DEFAULT (app_name()),
     SPID              int NOT NULL DEFAULT (@@spid),
     [SID]             varbinary(85) NULL,
     LogByApp          varchar(128) NULL,
     LogByAppSubSystem varchar(128) NULL,
     LogByDesc         varchar(128) NULL,
     LogText           varchar(6144) NULL,
     TraceText         varchar(max) NULL,
     TraceImage        varbinary(max) NULL,
     MiscVC1           varchar(128) NULL,
     MiscVC2           varchar(128) NULL,
     MiscVC3           varchar(128) NULL,
     MiscVC4           varchar(128) NULL,
     MiscInt1          int NULL,
     MiscInt2          int NULL,
     MiscInt3          int NULL,
     MiscInt4          int NULL,
     MiscTinyint1      tinyint NULL,
     MiscTinyint2      tinyint NULL,
     MiscTinyint3      tinyint NULL,
     MiscTinyint4      tinyint NULL,
     MiscBigInt1       bigint NULL,
     MiscBigInt2       bigint NULL,
     MiscBigInt3       bigint NULL,
     MiscBigInt4       bigint NULL,
     MiscDt1           datetime NULL,
     MiscDt2           datetime NULL,
     MiscDt3           datetime NULL,
     MiscDt4           datetime NULL,
     MiscGUID1         uniqueidentifier NULL,
     MiscGUID2         uniqueidentifier NULL,
     MiscGUID3         uniqueidentifier NULL,
     MiscGUID4         uniqueidentifier NULL,
     CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED ( EventLogId ASC )
)
GO

CREATE NONCLUSTERED INDEX IX_EventLog_App_AppSubSystem_Desc ON dbo.EventLog 
     ( LogByApp ASC, LogByAppSubSystem ASC, LogByDesc ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_AppName ON dbo.EventLog ( AppName ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_DatabaseName ON dbo.EventLog ( DatabaseName ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_EventLogTypeId ON dbo.EventLog ( EventLogTypeId ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_HostName ON dbo.EventLog ( HostName ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_LogDt ON dbo.EventLog ( LogDt ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_LoginName ON dbo.EventLog ( LoginName ASC )
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Event Log entry.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'EventLogId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Event Log Type.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'EventLogTypeId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date/time when event was logged.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogDt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Host Name of logged activity.  Caution!  Is not set by system therefore can be spoofed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'HostName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Server login name of logged event.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LoginName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Username in the database of logged event.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'DatabaseUserName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database name where activity was logged into.  For single application use will usually match database of application, for server level use will usually match database of calling application.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'DatabaseName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Application Name of logged activity.  Caution!  Is not set by system therefore can be spoofed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'AppName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Server Process Id of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'SPID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SID of user of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'SID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event Logged by Application' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogByApp'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event Logged by Application Sub-System.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogByAppSubSystem'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event Logged by Application Description, usually additional additional detail such as sub-sub-process, or internal identifier.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogByDesc'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Log message of the logged activity.  6K Max.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogText'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Traced text of logged activity.  2GB Max.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'TraceText'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Traced binary image of logged activity.  2GB Max.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'TraceImage'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscVC1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscVC2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscVC3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscVC4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscInt1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscInt2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscInt3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscInt4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscDt1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscDt2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscDt3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscDt4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event logging.  Contains logged events.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog'
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventLogType]') AND type in (N'U'))
BEGIN
     DROP TABLE dbo.EventLogType
END
GO

CREATE TABLE dbo.EventLogType(
     EventLogTypeId   int         NOT NULL,
     EventLogTypeCd   varchar(5)  NOT NULL,
     EventLogTypeDesc varchar(15) NOT NULL,
     CONSTRAINT PK_EventLogType PRIMARY KEY CLUSTERED ( EventLogTypeId ASC )
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_EventLogType_EventLogTypeCd] ON [dbo].[EventLogType] ([EventLogTypeCd])
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Event Log Type.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLogType', @level2type=N'COLUMN',@level2name=N'EventLogTypeId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Secondary Unique Identifier of a particular Event Log Type, user defined.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLogType', @level2type=N'COLUMN',@level2name=N'EventLogTypeCd'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Description of Event Log Type.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLogType', @level2type=N'COLUMN',@level2name=N'EventLogTypeDesc'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event logging types.  Contains list of event log types, normally: Unknown(0), Informational(1), Warning(2), and Error(3).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLogType'
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Seq]') AND type in (N'U'))
BEGIN
     DROP TABLE [dbo].[Seq]
END
GO

CREATE TABLE [dbo].[Seq](
	[SeqName]   [varchar](128) NOT NULL,
	[SeqNumber] [bigint] NOT NULL,
     CONSTRAINT [PK_Seq] PRIMARY KEY CLUSTERED ( [SeqName] ASC )
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Sequence Name.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Seq', @level2type=N'COLUMN',@level2name=N'SeqName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Value of the Sequence Name.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Seq', @level2type=N'COLUMN',@level2name=N'SeqNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains records for application controlled id values.  Effectively application controlled identity values.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Seq'
GO
IF EXISTS ( SELECT 'X' FROM sys.views WHERE name = 'sysdtslog90' AND schema_id = 1 )
BEGIN
     DROP VIEW [dbo].[sysdtslog90]
END
GO

CREATE VIEW [dbo].[sysdtslog90]
AS
/*
 *
 * sysdtslog90
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 06/07/2009, 1.00.0000
 *
 */
SELECT [id]          = EventLogId,
       [event]       = MiscVC1,
       [computer]    = MiscVC2,
       [operator]    = MiscVC3,
       [source]      = CONVERT( varchar(1024), TraceText ),
       [sourceid]    = MiscGUID1,
       [executionid] = MiscGUID4,
       [starttime]   = MiscDt1,
       [endtime]     = MiscDt2,
       [datacode]    = MiscInt1,
       [databytes]   = TraceImage,
       [message]     = CONVERT( varchar(2048), LogText )
FROM   dbo.EventLog
WHERE  LogByApp          = 'Integration Services'
AND    LogByAppSubSystem = 'sp_dts_addlogentry'
GO
IF EXISTS ( SELECT 'X' FROM sys.views WHERE name = 'sysssislog' AND schema_id = 1 )
BEGIN
     DROP VIEW [dbo].[sysssislog]
END
GO

CREATE VIEW [dbo].[sysssislog]
AS
/*
 *
 * sysssislog
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 06/07/2009, 1.00.0000
 *
 */
SELECT [id]          = EventLogId,
       [event]       = MiscVC1,
       [computer]    = MiscVC2,
       [operator]    = MiscVC3,
       [source]      = CONVERT( varchar(1024), TraceText ),
       [sourceid]    = MiscGUID1,
       [executionid] = MiscGUID4,
       [starttime]   = MiscDt1,
       [endtime]     = MiscDt2,
       [datacode]    = MiscInt1,
       [databytes]   = TraceImage,
       [message]     = CONVERT( varchar(2048), LogText )
FROM   dbo.EventLog
WHERE  LogByApp          = 'Integration Services'
AND    LogByAppSubSystem = 'sp_ssis_addlogentry'
GO
IF EXISTS ( SELECT 'X' FROM sys.views WHERE name = 'vDDLActivity' AND schema_id = 1 )
BEGIN
     DROP VIEW dbo.vDDLActivity
END
GO

CREATE VIEW dbo.vDDLActivity

AS
/*
 *
 * vDDLActivity
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/30/2009, 1.00.0000
 *
 */
SELECT DDLActivityId    = EventLogId,
       DDLActivityDt    = LogDt,
       HostName,
       AppName,
       LoginName,
       DatabaseUserName,
       EventType        = MiscVC1,
       DatabaseName,
       SchemaName       = MiscVC2,
       ObjectName       = MiscVC3,
       ObjectType       = MiscVC4,
       SPID,
       TSQLCommand      = TraceText 
FROM   dbo.EventLog 
WHERE  LogByApp          = 'LogAllDDL'
AND    LogByAppSubSystem = 'InsDDLActivity'
GO
IF EXISTS ( SELECT 'X' FROM sys.views WHERE name = 'vLogonActivity' AND schema_id = 1 )
BEGIN
     DROP VIEW dbo.vLogonActivity
END
GO

CREATE VIEW dbo.vLogonActivity

AS
/*
 *
 * vLogonActivity
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/30/2009, 1.00.0000
 *
 */
SELECT LoginActivityId  = EventLogId,
       LoginActivityDt  = MiscDt1,
       HostName,
       AppName,
       LoginName,
       LoginSID         = [SID],
       IsPooledFlag     = CONVERT( tinyint, MiscTinyint1 ),
       NetTransport     = MiscVC1,
       NetAddress       = MiscVC2,
       SPID
FROM   dbo.EventLog 
WHERE  LogByApp          = 'LogAllLogon'
AND    LogByAppSubSystem = 'InsLogonActivity'
GO
IF EXISTS ( SELECT 'X' FROM sys.views WHERE name = 'vPackageExecutionLog' AND schema_id = 1 )
BEGIN
     DROP VIEW dbo.vPackageExecutionLog
END
GO

CREATE VIEW dbo.vPackageExecutionLog
AS
/*
 *
 * vPackageExecutionLog
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/30/2009, 1.00.0000
 *
 */
SELECT PackageExecutionId       = MiscBigInt1,
       PackageExecutionDt       = LogDt,
       PackageName              = LogByDesc,
       PackageGUID              = MiscGUID1,
       ExecutionGUID            = MiscGUID4,
       PackageMajorVersion      = MiscInt1,
       PackageMinorVersion      = MiscInt2,
       PackageBuildVersion      = MiscInt3,
       MachineName              = MiscVC1,
       UserName                 = MiscVC2,
       ContainerStartTime       = MiscDt1,
       PackageStartTime         = MiscDt2,
       PackageEndTime           = MiscDt3,
       ParentPackageExecutionId = MiscBigInt2,
       PackageStateId           = MiscTinyInt1,
       PackageStateDesc         = CONVERT( varchar(10), CASE WHEN MiscTinyint1 = 1
                                                             THEN 'Start'
                                                             WHEN MiscTinyint1 = 128
                                                             THEN 'Info'
                                                             WHEN MiscTinyint1 = 255
                                                             THEN 'End'
                                                             ELSE 'Unknown'
                                                        END )
FROM   dbo.EventLog 
WHERE  LogByApp          = 'Integration Services'
AND    LogByAppSubSystem = 'InsPackageExecutionLog'
GO
ALTER TABLE dbo.EventLog ADD CONSTRAINT
     FK_EventLog_EventLogType FOREIGN KEY ( EventLogTypeId ) 
          REFERENCES dbo.EventLogType ( EventLogTypeId ) 
          ON UPDATE  NO ACTION 
          ON DELETE  NO ACTION 
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'trUpdCfgCtrl' AND type = 'TR')
     DROP TRIGGER dbo.trUpdCfgCtrl
GO

CREATE TRIGGER dbo.trUpdCfgCtrl ON dbo.CfgCtrl FOR UPDATE
/*
 *
 * trUpdCfgCtrl
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 06/11/2009, 1.00.0000
 *
 */
AS
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     -- Handle Last Mod Date Logic
     UPDATE [dbo].[CfgCtrl] 
     SET    [LastModDt] = CURRENT_TIMESTAMP 
     FROM   [dbo].[CfgCtrl] d, 
            inserted i 
     WHERE  i.[CfgCtrlKey] = d.[CfgCtrlKey]

     -- Get changed columns
     DECLARE @CU varbinary(255)
     SET @CU = COLUMNS_UPDATED()

     -- Declare table variable to hold flags indicating that that field changed
     -- in at least one record, we use this to cut the number of fields that the
     -- trigger must actually evaluate to determine whether there was a change.
     DECLARE @TABLE TABLE ( [CfgCtrlKey] tinyint NULL,
                            [CfgCtrlValue] tinyint NULL,
                            [CfgCtrlDescription] tinyint NULL,
                            [LastModDt] tinyint NULL )

     -- Populate the table variable with flags indicating all fields that have 
     -- Changed in any record, this handles the single or multi row update case
     INSERT @TABLE
          ( [CfgCtrlKey],
            [CfgCtrlValue],
            [CfgCtrlDescription],
            [LastModDt] )
     SELECT CASE WHEN SUBSTRING( @CU, 1, 1 ) & POWER(2, 0) > 0 THEN 1 ELSE 0 END,
            CASE WHEN SUBSTRING( @CU, 1, 1 ) & POWER(2, 1) > 0 THEN 1 ELSE 0 END,
            CASE WHEN SUBSTRING( @CU, 1, 1 ) & POWER(2, 2) > 0 THEN 1 ELSE 0 END,
            CASE WHEN SUBSTRING( @CU, 1, 1 ) & POWER(2, 3) > 0 THEN 1 ELSE 0 END

     -- Populate log table, handles changed rows without key changes (normal case)
     INSERT [dbo].[EventLog]
          ( [EventLogTypeId],
            [LogByApp],
            [LogByAppSubSystem],
            [LogByDesc],
            [LogText],
            [MiscVC1],
            [TraceText] )
     SELECT 1, -- Info
            'trUpdCfgCtrl', -- Trigger name
            '', 
            'CfgCtrl', -- Table name
            'Record Modification',
            'Non-Key Change',
            'XML' = ''
                  + '<RecordChange '
                  -- Key Field(s)
                  + 'CfgCtrlKey="'
                  + REPLACE( CONVERT( varchar(8000), i.[CfgCtrlKey] ), '"', '''' )
                  + '" '
                  -- Non-Key Field(s)
                  + CASE WHEN t.[CfgCtrlValue] = 1 
                          AND ( i.[CfgCtrlValue] != d.[CfgCtrlValue]
                             OR ( i.[CfgCtrlValue] IS     NULL AND d.[CfgCtrlValue] IS NOT NULL )
                             OR ( i.[CfgCtrlValue] IS NOT NULL AND d.[CfgCtrlValue] IS     NULL )
                              )
                         THEN 'Pre_CfgCtrlValue="' + ISNULL( REPLACE( CONVERT( varchar(8000), d.[CfgCtrlValue] ), '"', '''' ), '[NULL]' )
                            + '" Post_CfgCtrlValue="' + ISNULL( REPLACE( CONVERT( varchar(8000), i.[CfgCtrlValue] ), '"', '''' ), '[NULL]' )
                            + '" '
                         ELSE ''
                    END
                  + CASE WHEN t.[CfgCtrlDescription] = 1 
                          AND i.[CfgCtrlDescription] != d.[CfgCtrlDescription]
                         THEN 'Pre_CfgCtrlDescription="' + REPLACE( CONVERT( varchar(8000), d.[CfgCtrlDescription] ), '"', '''' )
                            + '" Post_CfgCtrlDescription="' + REPLACE( CONVERT( varchar(8000), i.[CfgCtrlDescription] ), '"', '''' )
                            + '" '
                         ELSE ''
                    END
                  + CASE WHEN t.[LastModDt] = 1 
                          AND i.[LastModDt] != d.[LastModDt]
                         THEN 'Pre_LastModDt="' + REPLACE( CONVERT( varchar(8000), d.[LastModDt] ), '"', '''' )
                            + '" Post_LastModDt="' + REPLACE( CONVERT( varchar(8000), i.[LastModDt] ), '"', '''' )
                            + '" '
                         ELSE ''
                    END
                  + '/>'
     FROM   inserted i, 
            deleted  d,
            @TABLE   t -- Pseudo Cartesian table only has 1 row
     WHERE  i.[CfgCtrlKey] = d.[CfgCtrlKey]

     -- Handle inserted side of key change
     INSERT [dbo].[EventLog]
          ( [EventLogTypeId],
            [LogByApp],
            [LogByAppSubSystem],
            [LogByDesc],
            [LogText],
            [MiscVC1],
            [MiscVC2],
            [TraceText] )
     SELECT 1, -- Info
            'trUpdCfgCtrl', -- Trigger name
            '', 
            'CfgCtrl', -- Table name
            'Record Modification',
            'Key Change',
            'Post Row',
            'XML' = ''
                  + '<RecordChange '
                  + 'CfgCtrlKey="'
                  + REPLACE( CONVERT( varchar(8000), i.[CfgCtrlKey] ), '"', '''' )
                  + '" '
                  + 'CfgCtrlValue="'
                  + ISNULL( REPLACE( CONVERT( varchar(8000), i.[CfgCtrlValue] ), '"', '''' ), '[NULL]' )
                  + '" '
                  + 'CfgCtrlDescription="'
                  + REPLACE( CONVERT( varchar(8000), i.[CfgCtrlDescription] ), '"', '''' )
                  + '" '
                  + 'LastModDt="'
                  + REPLACE( CONVERT( varchar(8000), i.[LastModDt] ), '"', '''' )
                  + '" '
                  + '/>'
     FROM   inserted i
     WHERE  NOT EXISTS ( SELECT 'X'
                         FROM   deleted d
                         WHERE  i.[CfgCtrlKey] = d.[CfgCtrlKey] )

     -- Handle delete side of key change
     INSERT [dbo].[EventLog]
          ( [EventLogTypeId],
            [LogByApp],
            [LogByAppSubSystem],
            [LogByDesc],
            [LogText],
            [MiscVC1],
            [MiscVC2],
            [TraceText] )
     SELECT 1, -- Info
            'trUpdCfgCtrl', -- Trigger name
            '', 
            'CfgCtrl', -- Table name
            'Record Modification',
            'Key Change',
            'Pre Row',
            'XML' = ''
                  + '<RecordChange '
                  + 'CfgCtrlKey="'
                  + REPLACE( CONVERT( varchar(8000), d.[CfgCtrlKey] ), '"', '''' )
                  + '" '
                  + 'CfgCtrlValue="'
                  + ISNULL( REPLACE( CONVERT( varchar(8000), d.[CfgCtrlValue] ), '"', '''' ), '[NULL]' )
                  + '" '
                  + 'CfgCtrlDescription="'
                  + REPLACE( CONVERT( varchar(8000), d.[CfgCtrlDescription] ), '"', '''' )
                  + '" '
                  + 'LastModDt="'
                  + REPLACE( CONVERT( varchar(8000), d.[LastModDt] ), '"', '''' )
                  + '" '
                  + '/>'
     FROM   deleted d
     WHERE  NOT EXISTS ( SELECT 'X'
                         FROM   inserted i
                         WHERE  i.[CfgCtrlKey] = d.[CfgCtrlKey] )
END
GO
IF EXISTS ( SELECT 'X' FROM sys.triggers WHERE name = 'trUpdEventLog' )
BEGIN
     DROP TRIGGER [dbo].[trUpdEventLog]
END
GO

CREATE TRIGGER [dbo].[trUpdEventLog] ON [dbo].[EventLog] 
     FOR UPDATE
AS
/*
 *
 * trUpdEventLog
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/10/2009, 1.00.0000
 *
 */
BEGIN
     RAISERROR( 'Updates are not allowed to the dbo.EventLog table.', 16, 1 )
     
     ROLLBACK TRANSACTION
END
GO
IF EXISTS ( SELECT 'X' FROM sys.procedures WHERE name = 'InsDDLActivity' AND schema_id = 1 )
BEGIN
     IF EXISTS ( SELECT 'X' 
                 FROM   sys.server_triggers 
                 WHERE  parent_class_desc = 'SERVER' 
                 AND    name              = 'LogAllDDL' 
                 AND    is_disabled       = 0 )
     BEGIN
          EXEC( 'DISABLE TRIGGER [LogAllDDL] ON ALL SERVER' )
     END

     DROP PROCEDURE dbo.InsDDLActivity
END
GO

CREATE PROCEDURE dbo.InsDDLActivity
     @EventData xml 
WITH ENCRYPTION 
AS
/*
 *
 * InsDDLActivity
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/29/2009, 1.00.0000
 *
 */
BEGIN
     SET XACT_ABORT ON
     SET NOCOUNT ON
     SET CONCAT_NULL_YIELDS_NULL ON

     INSERT dbo.EventLog
          ( EventLogTypeId,
            LogDt,
            LoginName,
            DatabaseUserName,
            DatabaseName,
            AppName,
            SPID,
            SID,
            LogByApp,
            LogByAppSubSystem,
            LogByDesc,
            LogText,
            TraceText,
            MiscVC1,
            MiscVC2,
            MiscVC3,
            MiscVC4 )
     SELECT 1, -- EventLogTypeId
            @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
            @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
            APP_NAME(),
            @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),
            SUSER_SID(@EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)')),
            'LogAllDDL', -- LogByApp
            'InsDDLActivity', -- LogByAppSubSystem
            '', -- LogByDesc
            '', -- LogText
            @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
            @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)')            
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts a DDL activity row into the EventLog table, this sproc is called from the Server Level ddl trigger.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = InsDDLActivity;
GO

GRANT EXEC ON dbo.InsDDLActivity TO AuditCredUser
GO

-- Enable the trigger
IF EXISTS ( SELECT 'X' 
            FROM   sys.server_triggers 
            WHERE  parent_class_desc = 'SERVER' 
            AND    name              = 'LogAllDDL' 
            AND    is_disabled       = 1 )
BEGIN
     EXEC ( 'ENABLE TRIGGER [LogAllDDL] ON ALL SERVER' )
END
GO
IF EXISTS ( SELECT 'X' FROM sys.procedures WHERE name = 'InsEventLog' AND schema_id = 1 )
BEGIN
     DROP PROCEDURE dbo.InsEventLog
END
GO

CREATE PROCEDURE dbo.InsEventLog
     @EventLogTypeId    int              = 0, -- Unknown
     @LogByApp          varchar(128)     = NULL,
     @LogByAppSubSystem varchar(128)     = NULL,
     @LogByDesc         varchar(128)     = NULL,
     @LogText           varchar(6144)    = NULL,
     @TraceText         varchar(max)     = NULL,
     @TraceImage        varbinary(max)   = NULL,
     @SID               varbinary(85)    = NULL,
     @MiscVC1           varchar(128)     = NULL,
     @MiscVC2           varchar(128)     = NULL,
     @MiscVC3           varchar(128)     = NULL,
     @MiscVC4           varchar(128)     = NULL,
     @MiscInt1          int              = NULL,
     @MiscInt2          int              = NULL,
     @MiscInt3          int              = NULL,
     @MiscInt4          int              = NULL,
     @MiscTinyInt1      tinyint          = NULL,
     @MiscTinyInt2      tinyint          = NULL,
     @MiscTinyInt3      tinyint          = NULL,
     @MiscTinyInt4      tinyint          = NULL,
     @MiscBigInt1       bigint           = NULL,
     @MiscBigInt2       bigint           = NULL,
     @MiscBigInt3       bigint           = NULL,
     @MiscBigInt4       bigint           = NULL,
     @MiscDt1           datetime         = NULL,
     @MiscDt2           datetime         = NULL,
     @MiscDt3           datetime         = NULL,
     @MiscDt4           datetime         = NULL,
     @MiscGUID1         uniqueidentifier = NULL,
     @MiscGUID2         uniqueidentifier = NULL,
     @MiscGUID3         uniqueidentifier = NULL,
     @MiscGUID4         uniqueidentifier = NULL
AS
/*
 *
 * InsEventLog
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/29/2009, 1.00.0000
 *
 */
BEGIN
     SET XACT_ABORT ON
     SET NOCOUNT ON

     IF NOT EXISTS ( SELECT 'X' 
                     FROM   dbo.EventLogType
                     WHERE  EventLogTypeId = @EventLogTypeId )
     BEGIN -- Force it to unknown, we'd rather get the event than lose it.
          SELECT @EventLogTypeId = 0 
     END
     
     INSERT dbo.EventLog
          ( EventLogTypeId,
            LogByApp,
            LogByAppSubSystem,
            LogByDesc,
            LogText,
            TraceText,
            TraceImage,
            SID,
            MiscVC1,
            MiscVC2,
            MiscVC3,
            MiscVC4,
            MiscInt1,
            MiscInt2,
            MiscInt3,
            MiscInt4,
            MiscTinyInt1,
            MiscTinyInt2,
            MiscTinyInt3,
            MiscTinyInt4,
            MiscBigInt1,
            MiscBigInt2,
            MiscBigInt3,
            MiscBigInt4,
            MiscDt1,
            MiscDt2,
            MiscDt3,
            MiscDt4,
            MiscGUID1,
            MiscGUID2,
            MiscGUID3,
            MiscGUID4 )
     SELECT @EventLogTypeId,
            @LogByApp,
            @LogByAppSubSystem,
            @LogByDesc,
            @LogText,
            @TraceText,
            @TraceImage,
            @SID,
            @MiscVC1,
            @MiscVC2,
            @MiscVC3,
            @MiscVC4,
            @MiscInt1,
            @MiscInt2,
            @MiscInt3,
            @MiscInt4,
            @MiscTinyInt1,
            @MiscTinyInt2,
            @MiscTinyInt3,
            @MiscTinyInt4,
            @MiscBigInt1,
            @MiscBigInt2,
            @MiscBigInt3,
            @MiscBigInt4,
            @MiscDt1,
            @MiscDt2,
            @MiscDt3,
            @MiscDt4,
            @MiscGUID1,
            @MiscGUID2,
            @MiscGUID3,
            @MiscGUID4
END
GO

GRANT EXEC ON dbo.InsEventLog TO guest
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts an event entry into the EventLog table.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = InsEventLog;
GO
IF EXISTS ( SELECT 'X' FROM sys.procedures WHERE name = 'InsLogonActivity' AND schema_id = 1 )
BEGIN
     IF EXISTS ( SELECT 'X' 
                 FROM   sys.server_triggers 
                 WHERE  parent_class_desc = 'SERVER' 
                 AND    name              = 'LogAllLogons' 
                 AND    is_disabled       = 0 )
     BEGIN
          EXEC( 'DISABLE TRIGGER [LogAllLogons] ON ALL SERVER' )
     END

     DROP PROCEDURE dbo.InsLogonActivity
END
GO

CREATE PROCEDURE dbo.InsLogonActivity
     @EventData xml 
WITH ENCRYPTION 
AS
/*
 *
 * InsLogonActivity
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/29/2009, 1.00.0000
 *
 */
BEGIN
     SET XACT_ABORT ON
     SET NOCOUNT ON
     
     DECLARE @SPID               int,
             @net_transport      varchar(40),
             @client_net_address varchar(48)

     SELECT @SPID = @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

     SELECT @net_transport      = net_transport,
            @client_net_address = client_net_address
     FROM   sys.dm_exec_connections 
     WHERE  session_id = @SPID

     INSERT dbo.EventLog
          ( EventLogTypeId,
            HostName,
            LoginName,
            DatabaseUserName,
            DatabaseName,
            AppName,
            SPID,
            SID,
            LogByApp,
            LogByAppSubSystem,
            LogByDesc,
            LogText,
            MiscVC1,
            MiscVC2,
            MiscDt1,
            MiscTinyint1 )
     SELECT 1, -- EventLogTypeId
            HostName = CASE WHEN @EventData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(128)') = N'<local machine>'
                            THEN HOST_NAME()
                            ELSE @EventData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(128)')
                       END,
            @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
            NULL, -- DatabaseUserName,
            NULL, -- DatabaseName
            APP_NAME(),
            @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),
            @EventData.value('(/EVENT_INSTANCE/SID)[1]', 'varbinary(85)'),
            'LogAllLogon', -- LogByApp
            'InsLogonActivity', -- LogByAppSubSystem
            '', -- LogByDesc
            '', -- LogText
            @net_transport,
            MiscVC2 = CASE WHEN @client_net_address = '<local machine>'
                           THEN HOST_NAME()
                           ELSE @client_net_address
                      END,
            @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
            @EventData.value('(/EVENT_INSTANCE/IsPooled)[1]', 'tinyint')
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts a logon activity row into the EventLog table, this sproc is called from the Server Level logon trigger.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = InsLogonActivity;
GO

GRANT EXEC ON dbo.InsLogonActivity TO AuditCredUser
GO

-- Enable the trigger
IF EXISTS ( SELECT 'X' 
            FROM   sys.server_triggers 
            WHERE  parent_class_desc = 'SERVER' 
            AND    name              = 'LogAllLogons' 
            AND    is_disabled       = 1 )
BEGIN
     EXEC ( 'ENABLE TRIGGER [LogAllLogons] ON ALL SERVER' )
END
GO
IF EXISTS ( SELECT 'X' FROM sys.procedures WHERE name = 'InsPackageExecutionLog' AND schema_id = 1 )
BEGIN
     DROP PROCEDURE dbo.InsPackageExecutionLog
END
GO

CREATE PROCEDURE dbo.InsPackageExecutionLog
     @PackageName                 nvarchar(255),
     @PackageExecutionGUID        varchar(38),
     @PackageMajorVersion         int,
     @PackageMinorVersion         int,
     @PackageBuildVersion         int,
     @MachineName                 nvarchar(128),
     @UserName                    nvarchar(128),
     @ContainerStartTime          datetime,
     @PackageStartTime            datetime,
     @PackageStartingFlag         tinyint       = 0, -- When both are zero it is 
     @PackageEndingFlag           tinyint       = 0, -- an info message.
     @ParentPackageExecutionId    bigint        = NULL,
     @ParentPackageExecutionIdStr varchar(30)   = NULL,
     @LogText                     varchar(6144) = NULL,
     @PackageExecutionId          bigint        = NULL OUTPUT,
     @PackageExecutionIdStr       varchar(30)   = NULL OUTPUT
AS
/*
 *
 * InsPackageExecutionLog
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/29/2009, 1.00.0000
 *
 */
BEGIN
     SET XACT_ABORT ON
     SET NOCOUNT ON

     -- Handle case of PackageExecutionId
     IF ( @PackageExecutionId IS NULL AND @PackageExecutionIdStr IS NOT NULL )
     BEGIN -- Id value ALWAYS takes precedence
          SELECT @PackageExecutionId = CONVERT( bigint, @PackageExecutionIdStr )
     END
     ELSE 
     BEGIN -- All other cases we set the string to match the id
          SELECT @PackageExecutionIdStr = CONVERT( varchar(30), @PackageExecutionId )
     END
     
     -- Handle case of ParentPackageExecutionId
     IF ( @ParentPackageExecutionId IS NULL AND @ParentPackageExecutionIdStr IS NOT NULL )
     BEGIN -- Id value ALWAYS takes precedence
          SELECT @ParentPackageExecutionId = CONVERT( bigint, @ParentPackageExecutionIdStr )
     END
     ELSE 
     BEGIN -- All other cases we set the string to match the id
          SELECT @ParentPackageExecutionIdStr = CONVERT( varchar(30), @ParentPackageExecutionId )
     END

     -- Condition Flags
     SELECT @PackageStartingFlag = CASE WHEN @PackageStartingFlag NOT IN ( 0, 1 )
                                        THEN 0
                                        ELSE @PackageStartingFlag
                                   END,
            @PackageEndingFlag   = CASE WHEN @PackageEndingFlag NOT IN ( 0, 1 )
                                        THEN 0
                                        ELSE @PackageEndingFlag
                                   END
     -- Handle Invalid flag combinations..
     SELECT @PackageStartingFlag = CASE WHEN @PackageStartingFlag = 1
                                         AND @PackageEndingFlag   = 1
                                        THEN 0
                                        ELSE @PackageStartingFlag
                                   END,
            @PackageEndingFlag   = CASE WHEN @PackageStartingFlag = 1
                                         AND @PackageEndingFlag   = 1
                                        THEN 0
                                        ELSE @PackageEndingFlag
                                   END

     -- Get the next package execution id from the SEQ table.  We do the an 
     -- optimistic commit with the expectation that the insert will succeed.
     -- given the way the insert occurs this is likely.  We do the commit 
     -- this way for concurrency reasons.  I don't wish to hold the lock any
     -- longer than I need to.
     IF ( @PackageStartingFlag = 1 )
     BEGIN
          BEGIN TRANSACTION

          UPDATE dbo.Seq 
          SET    SeqNumber = SeqNumber + 0 -- Lock record for a moment..
          WHERE  SeqName = 'PackageExecutionId'

          SELECT @PackageExecutionId = SeqNumber + 1
          FROM   dbo.Seq
          WHERE  SeqName = 'PackageExecutionId'
          
          UPDATE dbo.Seq
          SET   SeqNumber = @PackageExecutionId 
          FROM   dbo.Seq
          WHERE  SeqName = 'PackageExecutionId'

          COMMIT TRANSACTION
     END
     
     -- Insert it.
     INSERT dbo.EventLog 
          ( EventLogTypeId,
            LogByApp,
            LogByAppSubSystem,
            LogByDesc,
            LogText,
            MiscVC1,
            MiscVC2,
            MiscInt1,
            MiscInt2,
            MiscInt3,
            MiscBigInt1,
            MiscBigInt2,
            MiscDt1,
            MiscDt2,
            MiscDt3,
            MiscGUID4,
            MiscTinyInt1 )
     SELECT 1, -- EventLogTypeId
            'Integration Services', -- LogByApp
            'InsPackageExecutionLog', -- LogByAppSubSystem
            CONVERT( varchar(128), @PackageName ), -- LogByDesc
            LogText = CASE WHEN @PackageStartingFlag = 1
                            AND @PackageEndingFlag   = 0
                           THEN 'Package Starting'
                           WHEN @PackageStartingFlag = 0
                            AND @PackageEndingFlag   = 0
                           THEN @LogText 
                           WHEN @PackageStartingFlag = 0
                            AND @PackageEndingFlag   = 1
                           THEN 'Package Ending'
                           ELSE 'Package Action Unknown' -- Should not happen
                      END,
            CONVERT( varchar(128), @MachineName ), -- MiscVC1
            CONVERT( varchar(128), @UserName ), -- MiscVC2
            @PackageMajorVersion, -- MiscInt1
            @PackageMinorVersion, -- MiscInt2
            @PackageBuildVersion, -- MiscInt3
            MiscBigInt1 = @PackageExecutionId,
            MiscBigInt2 = CASE WHEN @ParentPackageExecutionId = -1
                               THEN NULL
                               ELSE @ParentPackageExecutionId
                          END, -- MiscBigInt2
            @ContainerStartTime, -- MiscDt1
            @PackageStartTime, -- MiscDt2
            MiscDt3 = CASE WHEN @PackageEndingFlag = 0
                           THEN NULL
                           WHEN @PackageEndingFlag = 1
                           THEN CURRENT_TIMESTAMP 
                           ELSE NULL -- Should not happen
                      END,
            @PackageExecutionGUID, -- MiscGUID4
            MiscTinyInt1 = CASE WHEN @PackageStartingFlag = 1
                                 AND @PackageEndingFlag   = 0
                                THEN 1 -- Start
                                WHEN @PackageStartingFlag = 0
                                 AND @PackageEndingFlag   = 0
                                THEN 128 -- Info Message
                                WHEN @PackageStartingFlag = 0
                                 AND @PackageEndingFlag   = 1
                                THEN 255 -- End
                                ELSE NULL -- Should not happen
                           END

     -- Get the value for the output variable and use it on the return
     SELECT @PackageExecutionId = CONVERT(bigint, @PackageExecutionId )
     SELECT @PackageExecutionIdStr = CONVERT( varchar(30), @PackageExecutionId )
          
     -- Return it
     SELECT PackageExecutionId          = @PackageExecutionId,
            PackageExecutionIdStr       = @PackageExecutionIdStr,
            ParentPackageExecutionId    = @ParentPackageExecutionId,
            ParentPackageExecutionIdStr = @ParentPackageExecutionIdStr,
            PackageStartingFlag         = @PackageStartingFlag,
            PackageEndingFlag           = @PackageEndingFlag
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts a SSIS package execution record into EventLog and returns a PackageExecutionId for the package to use.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = InsPackageExecutionLog;
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'PurgeEventLog' AND type = 'P')
     DROP PROCEDURE dbo.PurgeEventLog
GO

CREATE PROCEDURE dbo.PurgeEventLog
AS
/*
 *
 * PurgeEventLog
 *
 * Purpose:      To remove old entries in the EventLog table
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      Records may be deleted.
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: MaxEventLogDaysToKeep, number of days to keep records, 
 *               MaxEventLogIdCollectedByRepos, highest EventLogId that was
 *               collected by the repository collection process.
 *
 *               Record delete be prevented if the MaxEventLogIdCollectedByRepos
 *               is less than the date range that is to be deleted.  this value 
 *               does not have any effect on the collection process, it exists 
 *               to assist the purge process decide whether it should delete 
 *               records.  The main purpose is to prevent record deletion before
 *               they can be collected.
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 06/11/2009, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     EXEC dbo.InsEventLog @EventLogTypeId     = 1, -- Info
                          @LogByApp           = 'PurgeEventLog',
                          @LogText            = 'Process Starting'

     DECLARE @MaxEventLogDaysToKeep            bigint,
             @RepositoryInUseFlag              tinyint,
             @RepositoryInUseFlagStr           varchar(8000),
             @MaxEventLogIdCollectedByRepos    bigint,
             @MaxEventLogIdForDt               bigint,
             @BeforeEventLogId                 bigint,
             @DeleteBeforeDt                   datetime,
             @msg                              varchar(6144),
             @RC                               bigint

     -- Get stored paramters, but don't use the normal functions, read directly.
     SELECT @MaxEventLogDaysToKeep = CONVERT( int, CfgCtrlValue )
     FROM   dbo.CfgCtrl
     WHERE  CfgCtrlKey = 'MaxEventLogDaysToKeep'

     SELECT @MaxEventLogIdCollectedByRepos = CONVERT( bigint, CfgCtrlValue )
     FROM   dbo.CfgCtrl
     WHERE  CfgCtrlKey = 'MaxEventLogIdCollectedByRepos'

     SELECT @RepositoryInUseFlagStr = CfgCtrlValue 
     FROM   dbo.CfgCtrl
     WHERE  CfgCtrlKey = 'RepositoryInUseFlag'

     IF ( @RepositoryInUseFlagStr = '1' )
     BEGIN
          SET @RepositoryInUseFlag = 1
     END
     ELSE
     BEGIN
          SET @RepositoryInUseFlag = 0
     END

     -- Figure out what date we are working with..
     SELECT @DeleteBeforeDt = DATEADD( dd, -1 * ISNULL( @MaxEventLogDaysToKeep, 30 ), GETDATE() )

     -- Figure out what EventLogId that date that date range covers.
     SELECT @MaxEventLogIdForDt = MAX(EventLogId) 
     FROM   dbo.EventLog 
     WHERE  LogDt <= @DeleteBeforeDt

     -- Determine if we need to handle the Collection process preventing deletes
     IF ( @RepositoryInUseFlag = 1 )
     BEGIN -- Repository in use
          IF ( @MaxEventLogIdForDt <= @MaxEventLogIdCollectedByRepos )
          BEGIN -- The records in range for the date are less than what has been
                -- collected.
               SELECT @BeforeEventLogId = @MaxEventLogIdForDt
          END
          ELSE
          BEGIN -- The records in range for the date are above what has been 
                -- collected, so we'll find out what we can delete.
               -- Get the highest id that is in the date range we want AN below
               -- what has been picked up by the collection process.
               SELECT @BeforeEventLogId = MAX(EventLogId)
               FROM   dbo.EventLog 
               WHERE  EventLogId <= @MaxEventLogIdCollectedByRepos
               AND    LogDt      <= @DeleteBeforeDt
          END
     END
     ELSE
     BEGIN -- Repository NOT in use
          SET @BeforeEventLogId = @MaxEventLogIdForDt
     END

     -- Delete the records that are in scope, keeping in mind what has or has
     -- not been collected by the collection process..
     DELETE 
     FROM   dbo.EventLog
     WHERE  EventLogId <= @BeforeEventLogId 

     SELECT @RC = @@ROWCOUNT

     SELECT @msg = 'Process Ending.  @DeleteBeforeDt in MiscDt1, '
                 + '@MaxEventLogIdForDt in MiscBigInt1, '
                 + '@MaxEventLogIdCollectedByRepos in MiscBigInt2, '
                 + '@BeforeEventLogId in MiscBigInt3, '
                 + 'Records deleted count in MiscBigInt4, '
                 + '@RepositoryInUseFlag in MiscTinyInt1.'

     EXEC dbo.InsEventLog @EventLogTypeId     = 1, -- Info
                          @LogByApp           = 'PurgeEventLog',
                          @LogText            = @msg,
                          @MiscDt1            = @DeleteBeforeDt,
                          @MiscBigInt1        = @MaxEventLogIdForDt,
                          @MiscBigInt2        = @MaxEventLogIdCollectedByRepos,
                          @MiscBigInt3        = @BeforeEventLogId,
                          @MiscBigInt4        = @RC,
                          @MiscTinyInt1       = @RepositoryInUseFlag
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Purges records out of the eventlog table after they have been held for the required period of time.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = PurgeEventLog;
GO
IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'SaveCfgCtrl' AND type = 'P' AND uid = 1 )
     DROP PROCEDURE dbo.SaveCfgCtrl
GO

CREATE PROCEDURE dbo.SaveCfgCtrl
     @CfgCtrlKey            varchar(30), 
     @CfgCtrlValue          varchar(8000),
     @CfgCtrlDescription    varchar(128) = NULL, -- Optional entry
     @SuppressResultsetFlag tinyint      = 0
AS
/*
 *
 * SaveCfgCtrl
 *
 * Purpose:      To Add and update records to the CfgCtrl table.
 *
 * Inputs:       
 *
 * Assumes:      CfgCtrl table exists
 *
 * Effects:      Records are inserted or updated in CfgCtrl
 *
 * Returns:      CfgCtrlKey, CfgCtrlDescription, and CfgCtrlValue
 *
 * Calling
 * Application:  
 *
 * Note(s):      Post change values are returned for app verification of save.
 *
 *               The description, once written cannot be changed here.
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 01/06/2012, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     IF ( @CfgCtrlKey IS NULL ) 
     BEGIN
          RAISERROR( 'CfgCtrlKey must be specified.', 16, 1 )
     END
     ELSE
     BEGIN -- We got a value
          IF EXISTS ( SELECT 'X' FROM dbo.CfgCtrl WHERE CfgCtrlKey = @CfgCtrlKey )
          BEGIN -- Update
               UPDATE dbo.CfgCtrl
               SET    LastModDt    = CURRENT_TIMESTAMP,
                      CfgCtrlValue = LTRIM( RTRIM( @CfgCtrlValue ) )
               FROM   dbo.CfgCtrl
               WHERE  CfgCtrlKey = @CfgCtrlKey
          END
          ELSE
          BEGIN -- Insert
               INSERT dbo.CfgCtrl
                    ( CfgCtrlKey, 
                      CfgCtrlDescription, 
                      CfgCtrlValue )
               SELECT LTRIM( RTRIM( @CfgCtrlKey ) ),
                      ISNULL( LTRIM( RTRIM( @CfgCtrlDescription ) ), 'Unspecified' ),
                      LTRIM( RTRIM( @CfgCtrlValue ) )
          END
     END

     -- Output results if allowed
     IF ( @SuppressResultsetFlag != 1 )
     BEGIN
          SELECT CfgKey         = LTRIM( RTRIM( CfgCtrlKey ) ),
                 CfgDescription = LTRIM( RTRIM( CfgCtrlDescription ) ),
                 CfgValue       = LTRIM( RTRIM( CfgCtrlValue ) )
          FROM   dbo.CfgCtrl
          WHERE  CfgCtrlKey = @CfgCtrlKey
     END
END
GO
IF  EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dts_addlogentry]') AND type in (N'P', N'PC'))
BEGIN
     DROP PROCEDURE [dbo].[sp_dts_addlogentry]
END
GO

CREATE PROCEDURE [dbo].[sp_dts_addlogentry]
     @event       sysname,
     @computer    nvarchar(128),
     @operator    nvarchar(128),
     @source      nvarchar(1024),
     @sourceid    uniqueidentifier,
     @executionid uniqueidentifier,
     @starttime   datetime,
     @endtime     datetime,
     @datacode    int,
     @databytes   image,
     @message     nvarchar(2048)
AS
/*
 *
 * sp_dts_addlogentry
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 11/18/2011, 1.00.0000
 *
 */
BEGIN
     INSERT dbo.EventLog
          ( EventLogTypeId,
            LogByApp,
            LogByAppSubSystem,
            MiscVC1,
            MiscVC2,
            MiscVC3,
            TraceText,
            MiscGUID1,
            MiscGUID4,
            MiscDt1,
            MiscDt2,
            MiscInt1,
            MiscTinyint1,
            TraceImage,
            LogText )
     SELECT 1, -- Informational
            'Integration Services',
            'sp_dts_addlogentry',
            @event,
            @computer,
            @operator,
            @source,
            @sourceid,
            @executionid,
            @starttime,
            @endtime,
            @datacode,
            9, -- SSIS version, SQL 2005
            @databytes,
            @message
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts a SSIS (SQL 2005) log entry into the EventLog table.  This sproc name is the standard name that the SSIS SQL Server logging is expecting, instead of writing the data to a MS specifically named table it is written into the EventLog table.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = sp_dts_addlogentry;
GO
IF  EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ssis_addlogentry]') AND type in (N'P', N'PC'))
BEGIN
     DROP PROCEDURE [dbo].[sp_ssis_addlogentry]
END
GO

CREATE PROCEDURE [dbo].[sp_ssis_addlogentry]
     @event       sysname,
     @computer    nvarchar(128),
     @operator    nvarchar(128),
     @source      nvarchar(1024),
     @sourceid    uniqueidentifier,
     @executionid uniqueidentifier,
     @starttime   datetime,
     @endtime     datetime,
     @datacode    int,
     @databytes   image,
     @message     nvarchar(2048)
AS
/*
 *
 * sp_ssis_addlogentry
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 11/18/2011, 1.00.0000
 *
 */
BEGIN
     INSERT dbo.EventLog
          ( EventLogTypeId,
            LogByApp,
            LogByAppSubSystem,
            MiscVC1,
            MiscVC2,
            MiscVC3,
            TraceText,
            MiscGUID1,
            MiscGUID4,
            MiscDt1,
            MiscDt2,
            MiscInt1,
            MiscTinyint1,
            TraceImage,
            LogText )
     SELECT 1, -- Informational
            'Integration Services',
            'sp_ssis_addlogentry',
            @event,
            @computer,
            @operator,
            @source,
            @sourceid,
            @executionid,
            @starttime,
            @endtime,
            @datacode,
            10, -- SSIS version, SQL 2008
            @databytes,
            @message
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts a SSIS (SQL 2008) log entry into the EventLog table.  This sproc name is the standard name that the SSIS SQL Server logging is expecting, instead of writing the data to a MS specifically named table it is written into the EventLog table.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = sp_ssis_addlogentry;
GO
-- dbo.CfgCtrl
DELETE FROM dbo.CfgCtrl WHERE CfgCtrlKey = 'MaxEventLogDaysToKeep'
GO
DELETE FROM dbo.CfgCtrl WHERE CfgCtrlKey = 'MaxEventLogIdCollectedByRepos'
GO
DELETE FROM dbo.CfgCtrl WHERE CfgCtrlKey = 'RepositoryInUseFlag'
GO

INSERT dbo.CfgCtrl
     ( CfgCtrlKey, CfgCtrlValue, CfgCtrlDescription )
SELECT 'MaxEventLogDaysToKeep', '30', 'Maximum number of days to keep EventLog entries.'
UNION ALL
SELECT 'MaxEventLogIdCollectedByRepos', '0', 'Maximum EventLogId Collected By Repository.'
UNION ALL
SELECT 'RepositoryInUseFlag', '0', 'Flag indicating whether the Repository collection Process is in use.'
GO

-- Populate dbo.EventLogType
TRUNCATE TABLE dbo.EventLog
GO
DELETE FROM dbo.EventLogType 
GO

INSERT dbo.EventLogType 
     ( EventLogTypeId, EventLogTypeCd, EventLogTypeDesc ) 
SELECT 0, 'UNK', 'Unknown'
UNION ALL
SELECT 1, 'INFO', 'Informational'
UNION ALL
SELECT 2, 'WARN', 'Warning'
UNION ALL
SELECT 3, 'FAIL', 'Failure'
GO

-- dbo.Seq
TRUNCATE TABLE dbo.Seq
GO

INSERT dbo.Seq ( SeqName, SeqNumber ) VALUES ( 'PackageExecutionId', 0 )
GO
